﻿using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using MySqlConnector;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Threading.Tasks;

namespace Manon.Repository
{


    internal static class EntityFrameworkCoreExtensions
    {
        private static void CombineParams(ref DbCommand command, params object[] parameters)
        {
            if (parameters != null)
            {
                foreach (MySqlParameter parameter in parameters)
                {
                    if (!parameter.ParameterName.Contains("@"))
                        parameter.ParameterName = $"@{parameter.ParameterName}";
                    command.Parameters.Add(parameter);
                }
            }
        }

        private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection dbConn, params object[] parameters)
        {
            DbConnection conn = facade.GetDbConnection();
            dbConn = conn;
            conn.Open();
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            CombineParams(ref cmd, parameters);
            return cmd;
        }



        public static int SqlQueryScalar(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);
            var obj = cmd.ExecuteScalar();
            conn.Close();
            if (obj == null) return 0;
            return (int)obj;
        }

        /// <summary>
        /// 执行SQL查询，并返回第一行第一列的数据对象
        /// </summary>
        /// <param name="facade"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object? SqlQueryScalarObject(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);
            var obj = cmd.ExecuteScalar();
            conn.Close();
            return obj;
        }

        /// <summary>
        /// 执行SQL查询，并返回第一行第一列的数据对象
        /// </summary>
        /// <param name="facade"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static async Task<object?> SqlQueryScalarObjectAsync(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);
            var obj = await cmd.ExecuteScalarAsync();
            conn.Close();
            return obj;
        }

        public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);
            DbDataReader reader = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(reader);
            reader.Close();
            conn.Close();
            return dt;
        }


        public static IEnumerable<TEntity> SqlQuery<TEntity>(this DatabaseFacade facade, string sql, params object[] parameters) where TEntity : class, new()
        {
            DataTable dt = SqlQuery(facade, sql, parameters);
            return dt.ToEnumerable<TEntity>();
        }
        public static async Task<List<TEntity>> SqlQueryAsync<TEntity>(this DatabaseFacade facade, string sql, params object[] parameters) where TEntity : class, new()
        {
            DataTable dt = await SqlQueryAsync(facade, sql, parameters);
            return dt.ToList<TEntity>();
        }

        public static async Task<DataTable> SqlQueryAsync(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);
            DbDataReader reader = await cmd.ExecuteReaderAsync();
            DataTable dt = new DataTable();
            dt.Load(reader);
            reader.Close();
            conn.Close();
            return dt;
        }

        public static List<TEntity> ToList<TEntity>(this DataTable dt) where TEntity : class, new()
        {
            PropertyInfo[] propertyInfos = typeof(TEntity).GetProperties();
            List<TEntity> ts = new List<TEntity>();
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                TEntity t = new TEntity();
                foreach (PropertyInfo p in propertyInfos)
                {
                    if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                        p.SetValue(t, row[p.Name], null);
                }
                ts.Add(t);
                i++;
            }
            return ts;
        }

        public static IEnumerable<TEntity> ToEnumerable<TEntity>(this DataTable dt) where TEntity : class, new()
        {
            PropertyInfo[] propertyInfos = typeof(TEntity).GetProperties();
            TEntity[] ts = new TEntity[dt.Rows.Count];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                TEntity t = new TEntity();
                foreach (PropertyInfo p in propertyInfos)
                {
                    if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                        p.SetValue(t, row[p.Name], null);
                }
                ts[i] = t;
                i++;
            }
            return ts;
        }


        internal static string GetTotalCountSql(string Sql)
        {
            if (string.IsNullOrEmpty(Sql)) return Sql;

            Sql = Sql.Trim();

            if (!Sql.StartsWith("select")) return Sql;

            int index = Sql.IndexOf("from");
            return $"select count(1) as TotalCount {Sql.Substring(index, Sql.Length - index)}";

        }

        internal static string GetPagedSql(string Sql, int PageIndex, int IndexFrom, int PageSize)
        {
            if (string.IsNullOrEmpty(Sql)) return Sql;
            Sql = Sql.Replace(";", "");
            Sql = Sql.Trim();
            if (!Sql.StartsWith("select")) return Sql;

            int start = (PageIndex - IndexFrom) * PageSize;
            int end = start + PageSize;
            return $"{Sql} limit {start},{end}";

        }




        public static string ToSql<TEntity>(this IQueryable<TEntity> query)
        {
            var enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator();
            var relationalCommandCache = enumerator.Private("_relationalCommandCache");
            var selectExpression = relationalCommandCache.Private<SelectExpression>("_selectExpression");
            var factory = relationalCommandCache.Private<IQuerySqlGeneratorFactory>("_querySqlGeneratorFactory");

            var sqlGenerator = factory.Create();
            var command = sqlGenerator.GetCommand(selectExpression);

            string sql = command.CommandText;
            return sql;
        }

        private static object Private(this object obj, string privateField) => obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
        private static T Private<T>(this object obj, string privateField) => (T)obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);


    }

}


